/*
SELECT dbo.fn_ct_get_gpItemNumberByProcessStepID ( 1320 ) AS PHA
*/

ALTER  FUNCTION fn_ct_get_gpItemNumberByProcessStepID( @ProcessStep_ID INT )
RETURNS VARCHAR(31)
AS
BEGIN
	DECLARE @gp_itemNumber VARCHAR(31)
	DECLARE @itemNumber VARCHAR(50)
	DECLARE @processType VARCHAR(50)
	DECLARE @baseProcessType VARCHAR(50)

 	SET @gp_itemNumber = ''
	
 	
	SELECT @itemNumber = P.PART_NUMBER, @processType = PS.PS_Type, @baseProcessType = PS.PS_Type_Name 
	FROM PRODUCT P, Process_Step PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = @ProcessStep_ID

	EXEC @gp_itemNumber = fn_ct_get_gpItemNumber @itemNumber, @processType, @baseProcessType 

 	RETURN @gp_itemNumber
 END